package tt.dz.biz.dao.user;

import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.vo.OperListVO;
import tt.dz.vo.OperRoleVO;

@Repository("operListDAO")
public class OperListDAO
  extends BaseDAO
{
  private static final String LIST_ALL_OPERLIST = "select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a ";
  private static final String INSERT_OPERLIST = "insert into pub_oper_list(manager_id,mobile_no,regist_time,email,nick_name,name,gender,password,is_locked,locked_time,created_by,created_time,updated_by,updated_time,is_valid)  values(:managerId,:mobileNo,:registTime,:email,:nickName,:name,:gender,:password,:isLocked,:lockedTime,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid )";
  private static final String UPDATE_OPERLIST = "update  pub_oper_list set mobile_no = :mobileNo,regist_time = :registTime,email = :email,nick_name=:nickName,name = :name,gender = :gender,password = :password,is_locked = :isLocked,locked_time = :lockedTime,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid where manager_id = :managerId";
  private static final String QUERY_OPER = "select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a where a.mobile_no = :mobileNo and is_valid = 1";
  private static final String VALIDATE_OPER = "select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a where a.manager_id = :managerId and password = :password and is_valid = 1";
  private static final String QUERY_OPER_BY_SYS = " SELECT DISTINCT\tc.* from  pub_oper_role b LEFT JOIN pub_role a ON b.role_id = a.role_id LEFT JOIN pub_oper_list c ON c.manager_id = b.manager_id  WHERE b.sys_code = :sysCode and b.is_valid = 1 and c.manager_id is not null ";
  
  public List<OperListVO> listAllOperList()
  {
    return queryForList("select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid ,a.merchantCode from pub_oper_list as a  order by regist_time desc  ", null, OperListVO.class);
  }
  
  public List<OperListVO> queryOperList(OperListVO operListVO)
  {
    String sql = "select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a  where 1 = 1 " + assemblyConditions(operListVO, "a", true) + "  order by regist_time desc  ";
    operListVO = (OperListVO)convertObjectForLike(operListVO);
    return queryForList(sql, operListVO, OperListVO.class);
  }
  
  public List<OperListVO> queryOperListNotLike(OperListVO operListVO)
  {
    String sql = "select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a  where 1 = 1 " + assemblyConditions(operListVO, "a", false);
    return queryForList(sql, operListVO, OperListVO.class);
  }
  
  public int insertOperList(OperListVO operListVO)
  {
    return save("insert into pub_oper_list(manager_id,mobile_no,regist_time,email,nick_name,name,gender,password,is_locked,locked_time,created_by,created_time,updated_by,updated_time,is_valid,merchantCode)  values(:managerId,:mobileNo,:registTime,:email,:nickName,:name,:gender,:password,:isLocked,:lockedTime,:createdBy,:createdTime,:updatedBy,:updatedTime,:isValid ,:merchantCode)", operListVO);
  }
  
  public int updateOperList(OperListVO operListVO)
  {
    return save("update  pub_oper_list set mobile_no = :mobileNo,regist_time = :registTime,email = :email,nick_name=:nickName,name = :name,gender = :gender,password = :password,is_locked = :isLocked,locked_time = :lockedTime,created_by = :createdBy,created_time = :createdTime,updated_by = :updatedBy,updated_time = :updatedTime,is_valid = :isValid ,merchantCode=:merchantCode where manager_id = :managerId", operListVO);
  }
  
  public OperListVO queryOper(String aMobile)
  {
    OperListVO param = new OperListVO();
    param.setMobileNo(aMobile);
    OperListVO o;
    try
    {
      o = (OperListVO)queryForObject("select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a where a.mobile_no = :mobileNo and is_valid = 1", param, OperListVO.class);
    }
    catch (Exception e)
    {
      return null;
    }
    
    return o;
  }
  
  public List<OperListVO> queryOperForLogin(String aMobile)
  {
    OperListVO param = new OperListVO();
    param.setMobileNo(aMobile);
    param.setNickName(aMobile);
    param.setEmail(aMobile);
    StringBuilder sql = new StringBuilder("select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a ");
    sql.append("  where   is_valid = 1 and (mobile_no = :mobileNo or nick_name=:nickName or email=:email)");
    List<OperListVO> rsList = queryForList(sql.toString(), param, OperListVO.class);
    return rsList;
  }
  
  public OperListVO validateOper(OperListVO operListVO)
  {
    List<OperListVO> list = queryForList("select  a.manager_id,a.mobile_no,a.regist_time,a.nick_name,a.email,a.name,a.gender,a.password,a.is_locked,a.locked_time,a.created_by,a.created_time,a.updated_by,a.updated_time,a.is_valid  from pub_oper_list as a where a.manager_id = :managerId and password = :password and is_valid = 1", operListVO, OperListVO.class);
    if ((list != null) && (!list.isEmpty())) {
      return (OperListVO)list.get(0);
    }
    return null;
  }
  
  public List<OperListVO> queryOperListBySys(String sysCode, String startTime, String endTime)
  {
    StringBuilder sql = new StringBuilder(" SELECT DISTINCT\tc.* from  pub_oper_role b LEFT JOIN pub_role a ON b.role_id = a.role_id LEFT JOIN pub_oper_list c ON c.manager_id = b.manager_id  WHERE b.sys_code = :sysCode and b.is_valid = 1 and c.manager_id is not null ");
    if (!StringUtils.isBlank(startTime)) {
      sql.append(" and (c.created_time >= timestamp '" + startTime + "' or c.updated_time >= timestamp '" + startTime + "')");
    }
    if (!StringUtils.isBlank(endTime)) {
      sql.append(" and (c.created_time <= timestamp '" + endTime + "' or c.updated_time <= timestamp '" + endTime + "')");
    }
    OperRoleVO operRoleVO = new OperRoleVO();
    operRoleVO.setSysCode(sysCode);
    return queryForList(sql.toString(), operRoleVO, OperListVO.class);
  }
}
